import pandas as pandas
#import xlrd
#xls = xlrd.open_workbook(r'Stock_Data.xlsx', on_demand=True)
#print xls.sheet_names()
The sheet names are [u'Original', u'deduped', u'Sheet7', u'AmericanExchanges', u'ForeignAndPink', u'Sheet2']
data = pandas.read_excel('Stock_Data.xlsx', 'AmericanExchanges')
import copy
data_backup=copy.deepcopy(data)
First we create a date column instead of the integer formatting
data['Dates']=pandas.to_datetime(data['Names Date'].values, format = '%Y%m%d')
print 'The names of the columns are: \n'
for i in data.columns:
print i, '\n'
The names of the columns are: PERMNO Names Date Names Ending Date Share Code Exchange Code Standard Industrial Classification Code CUSIP Ticker Symbol Company Name Share Class Trading Symbol North American Industry Classification System Primary Exchange Trading Status Security Status PERMCO Nasdaq Issue Number Exchange Code Header Standard Industrial Classification Code.1 CUSIP Header Declaration Date Amount After Delisting Date of Delisting Payment Delisting Code Date of Next Available Information Payment Date Record Date Number of Shares Outstanding Header SIC Major Gorup Header SIC Industry Group Distribution Code Dividend Cash Amount Factor to Adjust Price Factor to Adjust Shares Acquiring PERMNO Acquiring PERMCO New PERMNO Delisting Return Without Dividends Delisting Price Delisting Return Nasdaq Traits Code Nasdaq National Market Indicator Market Maker Count Nasd Index Code Bid or Low Price Ask or High Price Price or Bid/Ask Average Volume Returns Bid Ask Shares Outstanding Cumulative Factor to Adjust Prices Cumulative Factor to Adjust Shares/Vol Price Alternate NASDAQ Number of Trades Returns without Dividends Value-Weighted Return-incl. dividends Value-Weighted Return-excl. dividends Equal-Weighted Return-incl. dividends Equal-Weighted Return-excl. dividends Return on the S&P 500 Index Dates
Let's check if any symbol has duplicate stocks
symbols = set(data['Ticker Symbol'].values)
dup_symbols = []
num_dupes = 0
for symbol in symbols:
cpnys = set(data['Company Name'].values[data['Ticker Symbol'].values == symbol])
if len(cpnys) >1:
dup_symbols.append(symbol)
print 'The symbol', symbol, 'has', len(cpnys), 'comapnies \n'
for i in cpnys:
print i, '\n'
num_dupes +=1
The symbol SIR has 4 comapnies STRUTHERS INDUSTRIES INC SELECT INCOME REIT SIRVA INC SIRROM CAPITAL CORP The symbol DURI has 2 comapnies DURCO INTERNATIONAL INC DURIRON INC The symbol ECOL has 2 comapnies U S ECOLOGY INC AMERICAN ECOLOGY CORP The symbol BK has 2 comapnies BANK NEW YORK INC BANK OF NEW YORK MELLON CORP The symbol FNF has 2 comapnies FIDELITY NATIONAL FINL INC NEW FIDELITY NATIONAL FINANCIAL INC The symbol EPG has 3 comapnies EL PASO NATURAL GAS CO EL PASO CORP EL PASO ENERGY CORP DEL The symbol MIR has 4 comapnies MIRAGE RESORTS INC M D C ASSET INVESTORS MIRANT CORP MIRANT CORP NEW The symbol RP has 3 comapnies REALPAGE INC ROONEY PACE GROUP INC RHONE POULENC S A The symbol AON has 2 comapnies AON CORP AON PLC The symbol AOI has 3 comapnies A O I COAL COMPANY ALLIANCE ONE INTERNATIONAL INC APPLE ORTHODONTIX INC The symbol PRU has 2 comapnies PRIME RESOURCES GROUP INC PRUDENTIAL FINANCIAL INC The symbol WCP has 2 comapnies WARNER COMPUTER SYS INC WARNER INSURANCE SERVICES INC The symbol ENDO has 2 comapnies ENDOCARE INC ENDOTRONICS INC The symbol RL has 3 comapnies RED LION HOTELS INC POLO RALPH LAUREN CORP RALPH LAUREN CORP The symbol RIG has 6 comapnies TRANSOCEAN INC NEW TRANSOCEAN SEDCO FOREX INC TRANSOCEAN LTD TRANSOCEAN OFFSHORE INC SONAT OFFSHORE DRILLING INC TRANSOCEAN INC The symbol CHL has 2 comapnies CHEMICAL NEW YORK CORP CHEMICAL BANKING CORP The symbol ESRX has 2 comapnies EXPRESS SCRIPTS INC EXPRESS SCRIPTS HOLDING CO The symbol MRK has 2 comapnies MERCK & CO INC MERCK & CO INC NEW The symbol SVU has 2 comapnies SUPER VALU STORES INC SUPERVALU INC The symbol HRC has 2 comapnies HEALTHSOUTH REHABILITATION CORP HEALTHSOUTH CORP The symbol WG has 3 comapnies WILLCOX & GIBBS INC WILLBROS GROUP INC WILLBROS GROUP INC DEL The symbol CCL has 2 comapnies CARNIVAL CORP CARNIVAL CRUISE LINES INC The symbol ITMN has 2 comapnies INTERMUNE PHARMACEUTICALS INTERMUNE INC The symbol TYC has 5 comapnies TYCO INTERNATIONAL LTD TYCO INTERNATIONAL LTD SWTZLND TYCO LABS INC TYCO INTERNATIONAL LTD BERMUDA TYCO INTERNATIONAL LTD NEW The symbol TISI has 2 comapnies TEAM INC TENET INFORMATION SERVICES INC The symbol ENDP has 2 comapnies ENDO HEALTH SOLUTIONS INC ENDO PHARMACEUTICALS HLDNGS INC The symbol IFT has 3 comapnies INCOME OPPORTUNITIES FD 2000 INC IMPERIAL HOLDINGS INC I F T CORP The symbol AET has 3 comapnies AETNA INC AETNA INC NEW AETNA LIFE & CASUALTY CO The symbol CVTY has 2 comapnies COVENTRY HEALTH CARE INC COVENTRY CORP The symbol FLS has 2 comapnies FLORIDA STEEL CORP FLOWSERVE CORP The symbol OMM has 2 comapnies O M I CORP NEW O M I CORP The symbol PCL has 2 comapnies PLUM CREEK TIMBER CO INC PLUM CREEK TIMBER CO L P The symbol JPM has 3 comapnies JPMORGAN CHASE & CO J P MORGAN CHASE & CO MORGAN J P & CO INC The symbol WCOM has 3 comapnies WORLDCOM INC GA NEW WORLDCOM INC GA M C I WORLDCOM INC The symbol AOL has 3 comapnies AMERICA ONLINE INC DEL A O L TIME WARNER INC A O L INC The symbol UPS has 2 comapnies UNITED PARCEL SERVICE INC AMERICUS TR FOR UNION PAC SHS The symbol PRDE has 2 comapnies PRIDE PETROLEUM SERVICES INC PRIDE INTERNATIONAL INC The symbol UVV has 2 comapnies UNIVERSAL CORPORATION UNIVERSAL LEAF TOB INC The symbol NWA has 2 comapnies NORTHWEST AIRLINES CORP N W A INC The symbol BMY has 2 comapnies BRISTOL MYERS CO BRISTOL MYERS SQUIBB CO The symbol MTG has 2 comapnies MORTGAGE GROWTH INVS M G I PROPERTIES The symbol JAZZ has 3 comapnies JAZZ PHARMACEUTICALS INC JAZZTEL PLC JAZZ PHARMACEUTICALS PLC The symbol MXWL has 2 comapnies MAXWELL LABORATORIES INC MAXWELL TECHNOLOGIES INC The symbol VJET has 2 comapnies VALUJET AIRLINES INC VALUJET INC The symbol ECHO has 2 comapnies ECHO GLOBAL LOGISTICS INC ELECTRONIC CLEARING HOUSE INC The symbol HCA has 5 comapnies H C A HOLDINGS INC HOSPITAL CORP AMERICA H C A HEALTHCARE CO H C A INC NEW H C A HOSPITAL CORP AMERICA The symbol SB has 2 comapnies SALOMON INC SAFE BULKERS INC The symbol EP has 2 comapnies ENSERCH EXPLORATION PARTNERS LTD EL PASO CORP The symbol TRW has 2 comapnies T R W INC T R W AUTOMOTIVE HOLDINGS CORP The symbol ASO has 2 comapnies AMSOUTH BANCORPORATION AIRSHARES E U CARBON ALLOWANCES The symbol MGI has 3 comapnies MONEYGRAM INTERNATIONAL INC M F S GOVERNMENT MARKETS INC TR M G I PROPERTIES The symbol HLS has 2 comapnies AMERICUS TR HEWLETT PACKARD SHS HEALTHSOUTH CORP The symbol MON has 3 comapnies MONEY STORE INC MONSANTO CO NEW MONARCH CAPITAL CORP The symbol ANR has 3 comapnies ALPHA NATURAL RESOURCES INC AVENOR INC ANNUITY & LIFE RE HOLDINGS LTD The symbol IGEN has 2 comapnies I G I INC IMMUNOGENETICS INC The symbol IIN has 2 comapnies I T T CORP I T T INDUSTRIES INC IND The symbol UB has 2 comapnies UNIONBANCAL CORP UNITED BRANDS CO The symbol NURO has 2 comapnies NEUROTECH CORP NEUROMETRIX INC The symbol EVI has 3 comapnies E V I WEATHERFORD INC ENERGY VENTURES INC E V I INC The symbol KMI has 2 comapnies KINDER MORGAN INC KINDER MORGAN INC KANSAS The symbol NE has 4 comapnies NOBLE DRILLING CORP NOBLE CORP BAAR NOBLE CORP NOBLE CORP PLC The symbol COB has 2 comapnies COLUMBIA LABORATORIES INC COMMUNITYONE BANCORP The symbol FE has 2 comapnies FRIES ENTERTAINMENT INC FIRSTENERGY CORP The symbol SMG has 3 comapnies SCOTTS COMPANY SCOTTS MIRACLE GRO CO SCIENCE MANAGEMENT CORP The symbol COL has 3 comapnies COLUMBIA HOSPITAL CORP COLUMBIA HEALTHCARE CORP COLUMBIA HCA HEALTHCARE CORP The symbol EEX has 2 comapnies E E X CORP ENSERCH EXPLORATION INC The symbol RRI has 4 comapnies RELIANT ENERGY INC RED ROOF INNS INC RELIANT RESOURCES INC R R I ENERGY INC The symbol HRLY has 2 comapnies HERLEY INDUSTRIES INC HERLEY MICROWAVE SYSTEMS INC The symbol AGAM has 2 comapnies A G A MEDICAL HOLDINGS INC ACRES GAMING INC The symbol ASH has 3 comapnies ASHLAND OIL INC ASHLAND INC NEW ASHLAND INC The symbol NT has 3 comapnies NORTEL NETWORKS CORP NORTHERN TELECOM LTD NORTEL NETWORKS CORP NEW The symbol SRA has 3 comapnies MERCK SERONO S A SERONO S A STRATUS COMPUTER INC The symbol ELN has 2 comapnies ELAN PLC ELAN CORP PLC The symbol CA has 2 comapnies C A INC COMPUTER ASSOCIATES INTL INC The symbol WAB has 3 comapnies WESTAMERICA BANCORPORATION WESTINGHOUSE AIR BRAKE CO NEW WABTEC CORP The symbol FRM has 3 comapnies FRIEDMANS INC FIRST MISSISSIPPI CORP FURMANITE CORP The symbol CVS has 4 comapnies AMERICUS TR CHEVRON SHS MELVILLE CORP C V S CORP C V S CAREMARK CORP The symbol IG has 2 comapnies I G I LABORATORIES INC I G I INC The symbol RDC has 2 comapnies ROWAN COMPANIES PLC ROWAN COMPANIES INC The symbol BBX has 2 comapnies B B X CAPITAL CORP BANKATLANTIC BANCORP INC The symbol PDE has 2 comapnies PRIDE INTERNATIONAL INC DEL PRIDE INTERNATIONAL INC The symbol CARE has 3 comapnies CARE GROUP INC INTERCARE INC CARESCIENCE INC The symbol MEL has 2 comapnies MELLON FINANCIAL CORP MELLON BANK CORP The symbol MMS has 2 comapnies MAXIMUS INC MID MAINE SAVINGS BANK FSB AUB The symbol EPL has 2 comapnies ENERGY PARTNERS LTD E P L OIL & GAS INC The symbol RIGS has 2 comapnies RIGGS NATIONAL CORP WASH D C ALPS E T F TRUST The symbol ITT has 3 comapnies I T T CORP I T T CORP NEV I T T INDUSTRIES INC IND The symbol DPS has 4 comapnies DAWSON PRODUCTION SERVICES INC DR PEPPER SEVEN UP CO INC AMERICUS TR DU PONT E I DE NEM DR PEPPER SNAPPLE GROUP INC The symbol CVX has 2 comapnies CHEVRON CORP NEW CHEVRONTEXACO CORP The symbol FNBN has 2 comapnies F N B UNITED CORP F N B CORP NC The symbol AAI has 2 comapnies AIRTRAN HOLDINGS INC AQUA ALLIANCE INC The symbol CDIC has 2 comapnies CARDINAL HEALTH INC CARDINAL DISTRIBUTION INC The symbol CVH has 2 comapnies CAVANAUGHS HOSPITALITY CORP COVENTRY HEALTH CARE INC The symbol KOP has 2 comapnies KOPPERS CO KOPPERS HOLDINGS INC The symbol AIC has 2 comapnies ASSET INVESTORS CORP DEL ASSET INVESTORS CORP The symbol PPC has 3 comapnies PILGRIMS PRIDE CORP NEW PILGRIMS PRIDE CORP PATRICK PETROLEUM CO The symbol ADES has 2 comapnies ADVANCED EMISSIONS SOLUTIONS INC ADA ES INC The symbol NTLI has 3 comapnies N T L INC N T L INC DEL N T L INC DEL NEW The symbol BPOP has 3 comapnies POPULAR INC BANPONCE CORP NEW BANCO POPULAR DE P R The symbol WFT has 3 comapnies WEATHERFORD INTL INC NEW WEATHERFORD INTL LTD WEATHERFORD INTL LTD NEW The symbol HON has 2 comapnies HONEYWELL INC HONEYWELL INTERNATIONAL INC The symbol IR has 3 comapnies INGERSOLL RAND PLC INGERSOLL RAND CO INGERSOLL RAND CO LTD The symbol AOR has 2 comapnies AURORA FOODS INC ISHARES TRUST The symbol WCG has 5 comapnies WELLCARE GROUP INC WELLCARE HEALTH PLANS INC WILLIS CORROON PLC WILLIS CORROON GROUP PLC WILLIAMS COMMUNICATIONS GRP INC The symbol SMCI has 2 comapnies SUPER MICRO COMPUTER INC SIMULATION SCIENCES INC The symbol PNU has 2 comapnies PHARMACIA & UPJOHN INC PNEUMATIC SCALE CORP The symbol RAE has 2 comapnies RAE SYSTEMS INC RAYMOND INDUSTRIES INC The symbol PNC has 3 comapnies P N C FINANCIAL CORP P N C BANK CORP P N C FINANCIAL SERVICES GRP INC The symbol UTSI has 2 comapnies UTSTARCOM HOLDINGS CORP UTSTARCOM INC The symbol MET has 3 comapnies MEMTEC LTD METROPOLITAN REALTY CORP METLIFE INC
num_dupes
276
all_companies = list(set(data['Company Name'].values))
for i in all_companies:
print str(i)
nan UNITED TECHNOLOGIES CORP EXACTECH INC AMERICAN LAND LEASE INC HERLEY MICROWAVE SYSTEMS INC TYCO INTERNATIONAL LTD SWTZLND COVER ALL TECHNOLOGIES INC FLOWSERVE CORP MONARCH CAPITAL CORP BARRETT RESOURCES CORP AMGEN INC WILLIS CORROON GROUP PLC NORWEST CORP COVENTRY CORP TOMMY HILFIGER CORP GOODRICH PETROLEUM CORP CELLEX BIOSCIENCES INC CARNIVAL CORP INGERSOLL RAND CO LTD AMERICAN ECOLOGY CORP ANGELES CORP ELITE PHARMACEUTICALS INC SELECT INCOME REIT LAS VEGAS SANDS CORP AMERICA ONLINE INC DEL FALCONSTOR SOFTWARE INC ENSERCH EXPLORATION INC RED LION HOTELS CORP BANK NEW YORK INC BANCO POPULAR DE P R TYCO INTERNATIONAL LTD NEW HEALTHSOUTH REHABILITATION CORP SARA LEE CORP PARKER DRILLING CO CHEMICAL BANKING CORP MERRILL LYNCH & CO INC AETNA INC CAVANAUGHS HOSPITALITY CORP TENET INFORMATION SERVICES INC ENDO PHARMACEUTICALS HLDNGS INC ENSERCH EXPLORATION PARTNERS LTD WRIGHT MEDICAL GROUP INC ING U S INC FLORIDA STEEL CORP CHEMTURA CORP COMBINED INTL CORP TRANSOCEAN INC NEW WESTAMERICA BANCORPORATION M G I PROPERTIES AMERICAN EXPRESS CO ASHLAND INC NEW TRIBUNE COMPANY NEW I F T CORP COMPUTER ASSOCIATES INTL INC SYMBOL TECHNOLOGIES INC CEPHALON INC WILLIAMS COMMUNICATIONS GRP INC K N ENERGY INC K B R INC WESTCOAST HOSPITALITY CORP AETNA INC NEW PATRICK PETROLEUM CO UNIVERSAL LEAF TOB INC DURIRON INC P N C BANK CORP ELAN PLC VALUJET AIRLINES INC DR PEPPER SEVEN UP CO INC P N C FINANCIAL SERVICES GRP INC HOSPITAL CORP AMERICA NORTEL NETWORKS CORP NEW MERCK SERONO S A JAZZ PHARMACEUTICALS INC MELVILLE CORP STRATUS COMPUTER INC EXXON MOBIL CORP HONEYWELL INTERNATIONAL INC SCHERING PLOUGH CORP BARD C R INC PRIME RESOURCES GROUP INC GENON ENERGY INC RIGGS NATIONAL CORP WASH D C CALIFORNIA FIRST BANK SAN FRAN UNION BANK SAN FRANCISCO CA ECHO GLOBAL LOGISTICS INC WEATHERFORD INTL INC NEW IMPERIAL HOLDINGS INC SCOTTS MIRACLE GRO CO CARNIVAL CRUISE LINES INC PRUDENTIAL FINANCIAL INC NORTHWEST AIRLINES CORP A O L INC MICRUS ENDOVASCULAR CORP RELIANT RESOURCES INC WORLDCOM INC GA NEW CHASE MANHATTAN CORP NEW U S ECOLOGY INC JAZZ PHARMACEUTICALS PLC UNIVERSAL CORPORATION CONAGRA INC AMERICAN ITALIAN PASTA CO N T L INC DEL NEW ALLIANCE ONE INTERNATIONAL INC SEARS HOLDINGS CORP AMERICUS TR DU PONT E I DE NEM NEUROMETRIX INC FOREST LABS INC AMERICUS TR CHEVRON SHS AIRSHARES E U CARBON ALLOWANCES OCTEL CORP E P L OIL & GAS INC ELECTRONIC CLEARING HOUSE INC TYSON FOODS INC TYCO INTERNATIONAL LTD CHEMICAL NEW YORK CORP CHIQUITA BRANDS INTL INC INGERSOLL RAND PLC FLUOR CORP MONEY STORE INC ANNUITY & LIFE RE HOLDINGS LTD AMSOUTH BANCORPORATION CARE GROUP INC PRIDE PETROLEUM SERVICES INC MAXIMUS INC SERONO S A J P MORGAN CHASE & CO H C A HOSPITAL CORP AMERICA ARAMARK HOLDINGS CORP COLUMBIA HOSPITAL CORP PILGRIMS PRIDE CORP N T L INC DEL A G C O CORP E V I WEATHERFORD INC STRUTHERS INDUSTRIES INC KAISER CEMENT CORP PHARMACIA & UPJOHN INC KOPPERS HOLDINGS INC BANKATLANTIC BANCORP INC PLUM CREEK TIMBER CO L P SALOMON INC ENDOTRONICS INC MARKS BROTHERS JEWELERS INC MERCK & CO INC AVENOR INC F N B CORP NC CHEVRON CORP O C O M CORP TITAN CORP CHEVRONTEXACO CORP H C A HOLDINGS INC BEAZER HOMES USA INC RED ROOF INNS INC GENERAL ELECTRIC CO H C A HEALTHCARE CO AURORA FOODS INC MERCK & CO INC NEW BANPONCE CORP NEW CROMPTON CORP HILLSHIRE BRANDS CO RAYMOND INDUSTRIES INC TYCO LABS INC RELIANT ENERGY INC PLUM CREEK TIMBER CO INC WILLBROS GROUP INC DEL URSTADT BIDDLE PROPERTIES INC CROMPTON & KNOWLES CORP MEMTEC LTD BANK OF NEW YORK MELLON CORP FRIES ENTERTAINMENT INC FARO TECHNOLOGIES INC SCOTTS COMPANY ALPS E T F TRUST MIRANT CORP UNITED PARCEL SERVICE INC AIRTRAN HOLDINGS INC T R W AUTOMOTIVE HOLDINGS CORP BRISTOL MYERS SQUIBB CO DIEBOLD INC GUIDANT CORP J B OXFORD HOLDINGS INC KINDER MORGAN INC ENERGY VENTURES INC H C A INC NEW MARTIN PROCESSING INC STRUTHERS OIL & GAS CORP L D D S COMMUNICATIONS INC GA E E X CORP HELMERICH & PAYNE INC FRIEDMANS INC ABBOTT LABORATORIES NASH FINCH COMPANY I T T CORP NEV IMMUNOGENETICS INC POLO RALPH LAUREN CORP XANSER CORP R R I ENERGY INC INTERNATIONAL CABLETEL INC A O I COAL COMPANY C V S CORP ADVANTAGE COS INC HEALTHSOUTH CORP ORTHOFIX INTERNATIONAL N V ADVANCED EMISSIONS SOLUTIONS INC DIMON INC ZIMMER HOLDINGS INC M D C ASSET INVESTORS ASSET INVESTORS CORP CHEVRON CORP NEW O M I CORP NEW REPUBLIC SERVICES INC WELLCARE GROUP INC WHITEHALL JEWELLERS INC FIRST MISSISSIPPI CORP GOOGLE INC EXPRESS SCRIPTS HOLDING CO TIME WARNER INC NEW PHELPS DODGE CORP BRISTOL MYERS CO SAFE BULKERS INC PFIZER INC ARCHER DANIELS MIDLAND CO WARNER COMPUTER SYS INC ENERGY PARTNERS LTD MID MAINE SAVINGS BANK FSB AUB COMVERSE TECHNOLOGY INC BANPONCE CORP KMART HOLDING CORP KOPPERS CO NORTEL NETWORKS CORP TRANSOCEAN LTD EL PASO ENERGY CORP DEL AMERICAN INTERNATIONAL GROUP INC COLUMBIA HEALTHCARE CORP COLUMBIA LABORATORIES INC MINSTAR INC FIRSTENERGY CORP WELLS FARGO & CO NEW COLUMBIA HCA HEALTHCARE CORP P N C FINANCIAL CORP PRIDE INTERNATIONAL INC DEL NOBLE CORP PLC SUPER VALU STORES INC UTSTARCOM INC KANEB SERVICES INC B B X CAPITAL CORP F N B UNITED CORP SCIENCE APPLICATIONS INTL CORP METLIFE INC INTERCARE INC RALPH LAUREN CORP R K S FINANCIAL GROUP INC DIBRELL BROTHERS INC MELLON BANK CORP WILLBROS GROUP INC SIMULATION SCIENCES INC DR PEPPER SNAPPLE GROUP INC MONSANTO CO NEW MAXWELL TECHNOLOGIES INC ACRES GAMING INC NEUROTECH CORP O M I CORP ROWAN COMPANIES INC AMERICAN ELECTRIC POWER CO INC TIDEWATER INC PRIDE INTERNATIONAL INC NOBLE CORP M C I WORLDCOM INC ROONEY PACE GROUP INC VALUJET INC RHONE POULENC S A I T T INDUSTRIES INC IND WESTINGHOUSE AIR BRAKE CO NEW AETNA LIFE & CASUALTY CO COVENTRY HEALTH CARE INC CARDINAL DISTRIBUTION INC UTSTARCOM HOLDINGS CORP CARESCIENCE INC EL PASO CORP EXPRESS SCRIPTS INC TYCO INTERNATIONAL LTD BERMUDA MAXWELL LABORATORIES INC MARINE TRANSPORT CORP ROWAN COMPANIES PLC WILLCOX & GIBBS INC SONESTA INTERNATIONAL HOTELS CP KINDER MORGAN INC KANSAS C K WITCO CORP PROPERTY CAPITAL TRUST NORTHERN TELECOM LTD AMERICUS TR FOR UNION PAC SHS UNITED BRANDS CO INTERMUNE INC C V S CAREMARK CORP FURMANITE CORP DAWSON PRODUCTION SERVICES INC TRANSOCEAN INC FIDELITY NATIONAL TITLE GP INC INCOME OPPORTUNITIES FD 2000 INC SPARTAN MOTORS INC L D D S COMMUNICATIONS INC I G I LABORATORIES INC ASHLAND OIL INC M T S SYSTEMS CORP COMMUNITYONE BANCORP AVENTIS TEAM INC MASSEY ENERGY CO TRANSOCEAN OFFSHORE INC ASHLAND INC BAKER HUGHES INC FIDELITY NATIONAL FINL INC NEW I G I INC RAE SYSTEMS INC HONEYWELL INC CARDINAL HEALTH INC JAZZTEL PLC ENDO HEALTH SOLUTIONS INC E V I INC REALPAGE INC UNIONBANCAL CORP P P G INDUSTRIES INC SCHNITZER STEEL INDUSTRIES INC ALPHA NATURAL RESOURCES INC M F S GOVERNMENT MARKETS INC TR QUEST DIAGNOSTICS INC LAPOLLA INDUSTRIES INC SONAT OFFSHORE DRILLING INC AMERICUS TR HEWLETT PACKARD SHS A G A MEDICAL HOLDINGS INC SCHLUMBERGER LTD ELAN CORP PLC A O L TIME WARNER INC EL PASO NATURAL GAS CO WORLDCOM INC GA METROPOLITAN REALTY CORP TRANSOCEAN SEDCO FOREX INC INNOSPEC INC AQUA ALLIANCE INC EXXON CORP ASSET INVESTORS CORP DEL WILLIS CORROON PLC OHIO EDISON CO INGERSOLL RAND CO N C S HEALTHCARE INC AON CORP WARNER INSURANCE SERVICES INC VIRGIN MEDIA INC MELLON FINANCIAL CORP I T T CORP SUPER MICRO COMPUTER INC PILGRIMS PRIDE CORP NEW MORGAN J P & CO INC OTRA SECURITIES GROUP INC MIRAGE RESORTS INC KAYDON CORP AIR & WATER TECHNOLOGIES CORP ALLERGAN INC URECOATS INDUSTRIES INC APPLE ORTHODONTIX INC MONEYGRAM INTERNATIONAL INC NOBLE CORP BAAR WEATHERFORD INTL LTD NEW KROGER COMPANY PNEUMATIC SCALE CORP LAWSON PRODUCTS INC ISHARES TRUST INTERMUNE PHARMACEUTICALS WELLCARE HEALTH PLANS INC RED LION HOTELS INC JOHNSON CONTROLS INC SUPERVALU INC BIOMET INC SIRROM CAPITAL CORP ENDOCARE INC SCIENCE MANAGEMENT CORP SIRVA INC DURCO INTERNATIONAL INC POPULAR INC JPMORGAN CHASE & CO PAMRAPO BANCORP INC JOHNSON & JOHNSON SOUTHERN ENERGY INC DEL HERLEY INDUSTRIES INC TEXTRON INC THORN APPLE VALLEY INC SPECTRANETICS CORP STRYKER CORP AON PLC NOBLE DRILLING CORP C A INC REXEL INC FIDELITY NATIONAL FINANCIAL INC T R W INC N W A INC ALLIED SIGNAL INC HALLIBURTON COMPANY N T L INC WABTEC CORP MIRANT CORP NEW GOLDEN NUGGET INC WEATHERFORD INTL LTD MORTGAGE GROWTH INVS ARTHROCARE CORP ADA ES INC
symbol_and_name = []
for symbol in symbols:
cpnys = set(data['Company Name'].values[data['Ticker Symbol'].values == symbol])
#if len(cpnys) >1:
if True:
symbol_and_name.append(symbol)
#print 'The symbol', symbol, 'has', len(cpnys), 'comapnies \n'
for i in cpnys:
print i,',', symbol
#num_dupes +=1
STRUTHERS INDUSTRIES INC , SIR SELECT INCOME REIT , SIR SIRVA INC , SIR SIRROM CAPITAL CORP , SIR AMERICAN EXPRESS CO , AXP FIDELITY NATIONAL TITLE GP INC , FNT DURCO INTERNATIONAL INC , DURI DURIRON INC , DURI ALLERGAN INC , AGN U S ECOLOGY INC , ECOL AMERICAN ECOLOGY CORP , ECOL BANK NEW YORK INC , BK BANK OF NEW YORK MELLON CORP , BK FIDELITY NATIONAL FINL INC NEW , FNF FIDELITY NATIONAL FINANCIAL INC , FNF BARRETT RESOURCES CORP , BARC WHITEHALL JEWELLERS INC , JWL BEAZER HOMES USA INC , BZH DAWSON PRODUCTION SERVICES INC , DPSI EL PASO NATURAL GAS CO , EPG EL PASO CORP , EPG EL PASO ENERGY CORP DEL , EPG MIRAGE RESORTS INC , MIR M D C ASSET INVESTORS , MIR MIRANT CORP , MIR MIRANT CORP NEW , MIR SOUTHERN ENERGY INC DEL , SOE COMVERSE TECHNOLOGY INC , CMVT FOREST LABS INC , FRX REALPAGE INC , RP ROONEY PACE GROUP INC , RP RHONE POULENC S A , RP KMART HOLDING CORP , KMRT AON CORP , AON AON PLC , AON HILLSHIRE BRANDS CO , HSH AIR & WATER TECHNOLOGIES CORP , AWT BANKATLANTIC BANCORP INC , BANCA A O I COAL COMPANY , AOI ALLIANCE ONE INTERNATIONAL INC , AOI APPLE ORTHODONTIX INC , AOI INNOSPEC INC , IOP PRIME RESOURCES GROUP INC , PRU PRUDENTIAL FINANCIAL INC , PRU WARNER COMPUTER SYS INC , WCP WARNER INSURANCE SERVICES INC , WCP COLUMBIA HOSPITAL CORP , CHOS ENDOCARE INC , ENDO ENDOTRONICS INC , ENDO RED LION HOTELS INC , RL POLO RALPH LAUREN CORP , RL RALPH LAUREN CORP , RL TRANSOCEAN INC NEW , RIG TRANSOCEAN SEDCO FOREX INC , RIG TRANSOCEAN LTD , RIG TRANSOCEAN OFFSHORE INC , RIG SONAT OFFSHORE DRILLING INC , RIG TRANSOCEAN INC , RIG CHEMTURA CORP , CHMT AIRTRAN HOLDINGS INC , AAIR O M I CORP , OMIC ING U S INC , VOYA TITAN CORP , TTN HALLIBURTON COMPANY , HAL REXEL INC , RXL CHIQUITA BRANDS INTL INC , CQB K N ENERGY INC , KNE CHEMICAL NEW YORK CORP , CHL CHEMICAL BANKING CORP , CHL EXPRESS SCRIPTS INC , ESRX EXPRESS SCRIPTS HOLDING CO , ESRX URECOATS INDUSTRIES INC , URT K B R INC , KBR GENERAL ELECTRIC CO , GE CHEVRON CORP , CHV MERCK & CO INC , MRK MERCK & CO INC NEW , MRK PILGRIMS PRIDE CORP , CHX COVER ALL TECHNOLOGIES INC , COVR SUPER VALU STORES INC , SVU SUPERVALU INC , SVU J B OXFORD HOLDINGS INC , JBOH HEALTHSOUTH REHABILITATION CORP , HRC HEALTHSOUTH CORP , HRC KANEB SERVICES INC , KAB MARKS BROTHERS JEWELERS INC , MBJI L D D S COMMUNICATIONS INC GA , LDDS WILLCOX & GIBBS INC , WG WILLBROS GROUP INC , WG WILLBROS GROUP INC DEL , WG CARNIVAL CORP , CCL CARNIVAL CRUISE LINES INC , CCL STRYKER CORP , SYK P N C FINANCIAL CORP , PNCF INTERMUNE PHARMACEUTICALS , ITMN INTERMUNE INC , ITMN MEMTEC LTD , MMTCY DIEBOLD INC , DBD TYCO INTERNATIONAL LTD , TYC TYCO INTERNATIONAL LTD SWTZLND , TYC TYCO LABS INC , TYC TYCO INTERNATIONAL LTD BERMUDA , TYC TYCO INTERNATIONAL LTD NEW , TYC WARNER INSURANCE SERVICES INC , WISI UNIONBANCAL CORP , UNBC URSTADT BIDDLE PROPERTIES INC , UBA TEAM INC , TISI TENET INFORMATION SERVICES INC , TISI VIRGIN MEDIA INC , VMED JOHNSON & JOHNSON , JNJ ANNUITY & LIFE RE HOLDINGS LTD , ALRE TYSON FOODS INC , TYSNA ENDO HEALTH SOLUTIONS INC , ENDP ENDO PHARMACEUTICALS HLDNGS INC , ENDP INCOME OPPORTUNITIES FD 2000 INC , IFT IMPERIAL HOLDINGS INC , IFT I F T CORP , IFT AMERICAN ELECTRIC POWER CO INC , AEP ORTHOFIX INTERNATIONAL N V , OFIX AETNA INC , AET AETNA INC NEW , AET AETNA LIFE & CASUALTY CO , AET COVENTRY HEALTH CARE INC , CVTY COVENTRY CORP , CVTY C K WITCO CORP , CNW NOBLE DRILLING CORP , NDCO WELLS FARGO & CO NEW , WFC FLORIDA STEEL CORP , FLS FLOWSERVE CORP , FLS FLUOR CORP , FLR CHASE MANHATTAN CORP NEW , CMB DIMON INC , DMN AMERICAN ITALIAN PASTA CO , AIPC WRIGHT MEDICAL GROUP INC , WMGI FALCONSTOR SOFTWARE INC , FALC SYMBOL TECHNOLOGIES INC , SMBL O M I CORP NEW , OMM O M I CORP , OMM PLUM CREEK TIMBER CO INC , PCL PLUM CREEK TIMBER CO L P , PCL INNOSPEC INC , IOSP PARKER DRILLING CO , PKD COLUMBIA LABORATORIES INC , COLB JPMORGAN CHASE & CO , JPM J P MORGAN CHASE & CO , JPM MORGAN J P & CO INC , JPM WORLDCOM INC GA NEW , WCOM WORLDCOM INC GA , WCOM M C I WORLDCOM INC , WCOM AMERICA ONLINE INC DEL , AOL A O L TIME WARNER INC , AOL A O L INC , AOL UNITED PARCEL SERVICE INC , UPS AMERICUS TR FOR UNION PAC SHS , UPS LAS VEGAS SANDS CORP , LVS PRIDE PETROLEUM SERVICES INC , PRDE PRIDE INTERNATIONAL INC , PRDE ANNUITY & LIFE RE HOLDINGS LTD , ALREF CHEMTURA CORP , CEM UNIVERSAL CORPORATION , UVV UNIVERSAL LEAF TOB INC , UVV SEARS HOLDINGS CORP , SHLD NORTHWEST AIRLINES CORP , NWA N W A INC , NWA PHELPS DODGE CORP , PD N T L INC , NLI BRISTOL MYERS CO , BMY BRISTOL MYERS SQUIBB CO , BMY BIOMET INC , BMET GENON ENERGY INC , GEN MORTGAGE GROWTH INVS , MTG M G I PROPERTIES , MTG JAZZ PHARMACEUTICALS INC , JAZZ JAZZTEL PLC , JAZZ JAZZ PHARMACEUTICALS PLC , JAZZ MAXWELL LABORATORIES INC , MXWL MAXWELL TECHNOLOGIES INC , MXWL NASH FINCH COMPANY , NAFC VALUJET AIRLINES INC , VJET VALUJET INC , VJET TRIBUNE COMPANY NEW , TRB ECHO GLOBAL LOGISTICS INC , ECHO ELECTRONIC CLEARING HOUSE INC , ECHO SPARTAN MOTORS INC , SPAR H C A HOLDINGS INC , HCA HOSPITAL CORP AMERICA , HCA H C A HEALTHCARE CO , HCA H C A INC NEW , HCA H C A HOSPITAL CORP AMERICA , HCA SALOMON INC , SB SAFE BULKERS INC , SB AON CORP , AOC INTERNATIONAL CABLETEL INC , ICTL ENSERCH EXPLORATION PARTNERS LTD , EP EL PASO CORP , EP T R W INC , TRW T R W AUTOMOTIVE HOLDINGS CORP , TRW MONEY STORE INC , MONE STRYKER CORP , STRY MARINE TRANSPORT CORP , MTLX SCIENCE APPLICATIONS INTL CORP , SAIC M F S GOVERNMENT MARKETS INC TR , MGF KAISER CEMENT CORP , KCC AMSOUTH BANCORPORATION , ASO AIRSHARES E U CARBON ALLOWANCES , ASO AMERICAN LAND LEASE INC , ANL MONEYGRAM INTERNATIONAL INC , MGI M F S GOVERNMENT MARKETS INC TR , MGI M G I PROPERTIES , MGI AMERICUS TR HEWLETT PACKARD SHS , HLS HEALTHSOUTH CORP , HLS THORN APPLE VALLEY INC , TAVI MARTIN PROCESSING INC , MPI MONEY STORE INC , MON MONSANTO CO NEW , MON MONARCH CAPITAL CORP , MON ALPHA NATURAL RESOURCES INC , ANR AVENOR INC , ANR ANNUITY & LIFE RE HOLDINGS LTD , ANR I G I INC , IGEN IMMUNOGENETICS INC , IGEN STRATUS COMPUTER INC , STRA I T T CORP , IIN I T T INDUSTRIES INC IND , IIN SCOTTS COMPANY , SCTT UNIONBANCAL CORP , UB UNITED BRANDS CO , UB WESTAMERICA BANCORPORATION , WSAM TYSON FOODS INC , TSN XANSER CORP , XNR NEUROTECH CORP , NURO NEUROMETRIX INC , NURO E V I WEATHERFORD INC , EVI ENERGY VENTURES INC , EVI E V I INC , EVI AMGEN INC , AMGN KINDER MORGAN INC , KMI KINDER MORGAN INC KANSAS , KMI NOBLE DRILLING CORP , NE NOBLE CORP BAAR , NE NOBLE CORP , NE NOBLE CORP PLC , NE COMBINED INTL CORP , PMA ENERGY VENTURES INC , ENGY COLUMBIA LABORATORIES INC , COB COMMUNITYONE BANCORP , COB FRIES ENTERTAINMENT INC , FE FIRSTENERGY CORP , FE OCTEL CORP , OTL SCOTTS COMPANY , SMG SCOTTS MIRACLE GRO CO , SMG SCIENCE MANAGEMENT CORP , SMG TEAM INC , TMI STRUTHERS OIL & GAS CORP , STOG COLUMBIA HOSPITAL CORP , COL COLUMBIA HEALTHCARE CORP , COL COLUMBIA HCA HEALTHCARE CORP , COL HEALTHSOUTH REHABILITATION CORP , HSRC E E X CORP , EEX ENSERCH EXPLORATION INC , EEX DIBRELL BROTHERS INC , DBRL KAYDON CORP , KDN SIRROM CAPITAL CORP , SROM WHITEHALL JEWELLERS INC , WHJI RELIANT ENERGY INC , RRI RED ROOF INNS INC , RRI RELIANT RESOURCES INC , RRI R R I ENERGY INC , RRI KAYDON CORP , KDON HERLEY INDUSTRIES INC , HRLY HERLEY MICROWAVE SYSTEMS INC , HRLY WESTAMERICA BANCORPORATION , WABC A G A MEDICAL HOLDINGS INC , AGAM ACRES GAMING INC , AGAM JOHNSON CONTROLS INC , JCI ARTHROCARE CORP , ARTC ARAMARK HOLDINGS CORP , ARMK SCHNITZER STEEL INDUSTRIES INC , SCHN OTRA SECURITIES GROUP INC , OSGI ASHLAND OIL INC , ASH ASHLAND INC NEW , ASH ASHLAND INC , ASH NORTEL NETWORKS CORP , NT NORTHERN TELECOM LTD , NT NORTEL NETWORKS CORP NEW , NT HELMERICH & PAYNE INC , HP LAWSON PRODUCTS INC , LAWS CROMPTON CORP , CK ZIMMER HOLDINGS INC , ZMH MERCK SERONO S A , SRA SERONO S A , SRA STRATUS COMPUTER INC , SRA ELAN PLC , ELN ELAN CORP PLC , ELN C A INC , CA COMPUTER ASSOCIATES INTL INC , CA EXACTECH INC , EXAC WESTAMERICA BANCORPORATION , WAB WESTINGHOUSE AIR BRAKE CO NEW , WAB WABTEC CORP , WAB FRIEDMANS INC , FRM FIRST MISSISSIPPI CORP , FRM FURMANITE CORP , FRM AMERICUS TR CHEVRON SHS , CVS MELVILLE CORP , CVS C V S CORP , CVS C V S CAREMARK CORP , CVS CEPHALON INC , CEPH ANGELES CORP , ANG I G I LABORATORIES INC , IG I G I INC , IG PROPERTY CAPITAL TRUST , PCT ROWAN COMPANIES PLC , RDC ROWAN COMPANIES INC , RDC B B X CAPITAL CORP , BBX BANKATLANTIC BANCORP INC , BBX UNION BANK SAN FRANCISCO CA , UBNK PRIDE INTERNATIONAL INC DEL , PDE PRIDE INTERNATIONAL INC , PDE ARCHER DANIELS MIDLAND CO , ADM BANPONCE CORP , BDEP CARE GROUP INC , CARE INTERCARE INC , CARE CARESCIENCE INC , CARE MASSEY ENERGY CO , MEE GUIDANT CORP , GDT RED LION HOTELS CORP , RLH MELLON FINANCIAL CORP , MEL MELLON BANK CORP , MEL ALLIED SIGNAL INC , ALD BARRETT RESOURCES CORP , BRR MAXIMUS INC , MMS MID MAINE SAVINGS BANK FSB AUB , MMS FRIEDMANS INC , FRDM KROGER COMPANY , KR ENERGY PARTNERS LTD , EPL E P L OIL & GAS INC , EPL AMERICAN ITALIAN PASTA CO , PLB MELVILLE CORP , MES MERRILL LYNCH & CO INC , MER OHIO EDISON CO , OEC RIGGS NATIONAL CORP WASH D C , RIGS ALPS E T F TRUST , RIGS I T T CORP , ITT I T T CORP NEV , ITT I T T INDUSTRIES INC IND , ITT TIDEWATER INC , TDW SYMBOL TECHNOLOGIES INC , SBL GOLDEN NUGGET INC , GNG REPUBLIC SERVICES INC , RSG DAWSON PRODUCTION SERVICES INC , DPS DR PEPPER SEVEN UP CO INC , DPS AMERICUS TR DU PONT E I DE NEM , DPS DR PEPPER SNAPPLE GROUP INC , DPS MONEY STORE INC , MSI CHEVRON CORP NEW , CVX CHEVRONTEXACO CORP , CVX FRIEDMANS INC , FRDMA F N B UNITED CORP , FNBN F N B CORP NC , FNBN ELITE PHARMACEUTICALS INC , ELI FARO TECHNOLOGIES INC , FARO TOMMY HILFIGER CORP , TOM AIRTRAN HOLDINGS INC , AAI AQUA ALLIANCE INC , AAI CARDINAL HEALTH INC , CDIC CARDINAL DISTRIBUTION INC , CDIC ELAN PLC , ELAN CARDINAL HEALTH INC , CAH NORWEST CORP , NOB CAVANAUGHS HOSPITALITY CORP , CVH COVENTRY HEALTH CARE INC , CVH CELLEX BIOSCIENCES INC , CLXX SPECTRANETICS CORP , SPNC KOPPERS CO , KOP KOPPERS HOLDINGS INC , KOP AMERICAN INTERNATIONAL GROUP INC , AIG AMERICA ONLINE INC DEL , AMER CONAGRA INC , CAG TIME WARNER INC NEW , TWX ASSET INVESTORS CORP DEL , AIC ASSET INVESTORS CORP , AIC PILGRIMS PRIDE CORP NEW , PPC PILGRIMS PRIDE CORP , PPC PATRICK PETROLEUM CO , PPC P P G INDUSTRIES INC , PPG A G C O CORP , AGCO ADVANCED EMISSIONS SOLUTIONS INC , ADES ADA ES INC , ADES MINSTAR INC , MNST SCHERING PLOUGH CORP , SGP QUEST DIAGNOSTICS INC , DGX R K S FINANCIAL GROUP INC , RKSF N T L INC , NTLI N T L INC DEL , NTLI N T L INC DEL NEW , NTLI BARD C R INC , BCR POPULAR INC , BPOP BANPONCE CORP NEW , BPOP BANCO POPULAR DE P R , BPOP WESTCOAST HOSPITALITY CORP , WEH WEATHERFORD INTL INC NEW , WFT WEATHERFORD INTL LTD , WFT WEATHERFORD INTL LTD NEW , WFT L D D S COMMUNICATIONS INC , LDDSA TEXTRON INC , TXT GOOGLE INC , GOOG MID MAINE SAVINGS BANK FSB AUB , MMSB CALIFORNIA FIRST BANK SAN FRAN , CFBK ORTHOFIX INTERNATIONAL N V , OFIXF COLUMBIA LABORATORIES INC , CBRX A G C O CORP , AG HONEYWELL INC , HON HONEYWELL INTERNATIONAL INC , HON ADVANTAGE COS INC , ADCO INGERSOLL RAND PLC , IR INGERSOLL RAND CO , IR INGERSOLL RAND CO LTD , IR AURORA FOODS INC , AOR ISHARES TRUST , AOR O C O M CORP , OHCO ABBOTT LABORATORIES , ABT WELLCARE GROUP INC , WCG WELLCARE HEALTH PLANS INC , WCG WILLIS CORROON PLC , WCG WILLIS CORROON GROUP PLC , WCG WILLIAMS COMMUNICATIONS GRP INC , WCG AVENTIS , AVE EXXON CORP , XON SARA LEE CORP , SLE UNITED TECHNOLOGIES CORP , UTX EXXON MOBIL CORP , XOM LAPOLLA INDUSTRIES INC , LPA CROMPTON & KNOWLES CORP , CNK SCHLUMBERGER LTD , SLB MICRUS ENDOVASCULAR CORP , MEND BAKER HUGHES INC , BHI SUPER MICRO COMPUTER INC , SMCI SIMULATION SCIENCES INC , SMCI PFIZER INC , PFE SONESTA INTERNATIONAL HOTELS CP , SNSTA GOODRICH PETROLEUM CORP , GDP PHARMACIA & UPJOHN INC , PNU PNEUMATIC SCALE CORP , PNU N C S HEALTHCARE INC , NCSS M T S SYSTEMS CORP , MTSC RAE SYSTEMS INC , RAE RAYMOND INDUSTRIES INC , RAE PAMRAPO BANCORP INC , PBCI P N C FINANCIAL CORP , PNC P N C BANK CORP , PNC P N C FINANCIAL SERVICES GRP INC , PNC UTSTARCOM HOLDINGS CORP , UTSI UTSTARCOM INC , UTSI MEMTEC LTD , MET METROPOLITAN REALTY CORP , MET METLIFE INC , MET
to_drop = pandas.read_excel('Bad_Names_Sheet_2.xlsx', 'Sheet1')
indexed_df = data.set_index(data['Company Name'])
to_drop.head()
| Company | Ticker | |
|---|---|---|
| 0 | A G C O CORP | AG |
| 1 | A O I COAL COMPANY | AOI |
| 2 | ACRES GAMING INC | AGAM |
| 3 | ADVANCED EMISSIONS SOLUTIONS INC | ADES |
| 4 | ADVANTAGE COS INC | ADCO |
5 rows × 2 columns
cleaned_data = indexed_df.drop(to_drop['Company'].values)
set(cleaned_data['Company Name'])
{nan,
u'A G A MEDICAL HOLDINGS INC',
u'A O L INC',
u'A O L TIME WARNER INC',
u'ABBOTT LABORATORIES',
u'ADA ES INC',
u'AETNA INC',
u'AETNA INC NEW',
u'AETNA LIFE & CASUALTY CO',
u'ALLERGAN INC',
u'ALLIANCE ONE INTERNATIONAL INC',
u'ALPHA NATURAL RESOURCES INC',
u'AMERICA ONLINE INC DEL',
u'AMERICAN ECOLOGY CORP',
u'AMERICAN ELECTRIC POWER CO INC',
u'AMERICAN EXPRESS CO',
u'AMERICAN INTERNATIONAL GROUP INC',
u'AMGEN INC',
u'AMSOUTH BANCORPORATION',
u'AON PLC',
u'AQUA ALLIANCE INC',
u'ARAMARK HOLDINGS CORP',
u'ARCHER DANIELS MIDLAND CO',
u'ARTHROCARE CORP',
u'ASHLAND INC',
u'ASHLAND INC NEW',
u'ASHLAND OIL INC',
u'AURORA FOODS INC',
u'BAKER HUGHES INC',
u'BANCO POPULAR DE P R',
u'BANK NEW YORK INC',
u'BANK OF NEW YORK MELLON CORP',
u'BARD C R INC',
u'BEAZER HOMES USA INC',
u'BIOMET INC',
u'BRISTOL MYERS CO',
u'BRISTOL MYERS SQUIBB CO',
u'C A INC',
u'C V S CAREMARK CORP',
u'C V S CORP',
u'CARNIVAL CORP',
u'CARNIVAL CRUISE LINES INC',
u'CEPHALON INC',
u'CHEMTURA CORP',
u'CHEVRON CORP NEW',
u'CHEVRONTEXACO CORP',
u'CHIQUITA BRANDS INTL INC',
u'COMMUNITYONE BANCORP',
u'COMPUTER ASSOCIATES INTL INC',
u'COMVERSE TECHNOLOGY INC',
u'CONAGRA INC',
u'COVENTRY CORP',
u'COVENTRY HEALTH CARE INC',
u'CROMPTON & KNOWLES CORP',
u'DIEBOLD INC',
u'DR PEPPER SEVEN UP CO INC',
u'DR PEPPER SNAPPLE GROUP INC',
u'ELAN CORP PLC',
u'ELECTRONIC CLEARING HOUSE INC',
u'ENDO HEALTH SOLUTIONS INC',
u'ENDO PHARMACEUTICALS HLDNGS INC',
u'ENDOTRONICS INC',
u'ENERGY PARTNERS LTD',
u'EXACTECH INC',
u'EXPRESS SCRIPTS HOLDING CO',
u'EXPRESS SCRIPTS INC',
u'EXXON MOBIL CORP',
u'FALCONSTOR SOFTWARE INC',
u'FARO TECHNOLOGIES INC',
u'FIDELITY NATIONAL FINANCIAL INC',
u'FIDELITY NATIONAL FINL INC NEW',
u'FLOWSERVE CORP',
u'FOREST LABS INC',
u'FRIEDMANS INC',
u'GENERAL ELECTRIC CO',
u'GOOGLE INC',
u'GUIDANT CORP',
u'H C A HEALTHCARE CO',
u'H C A HOLDINGS INC',
u'H C A HOSPITAL CORP AMERICA',
u'H C A INC NEW',
u'HEALTHSOUTH CORP',
u'HELMERICH & PAYNE INC',
u'HERLEY INDUSTRIES INC',
u'HERLEY MICROWAVE SYSTEMS INC',
u'HONEYWELL INC',
u'HONEYWELL INTERNATIONAL INC',
u'HOSPITAL CORP AMERICA',
u'I G I LABORATORIES INC',
u'IMPERIAL HOLDINGS INC',
u'ING U S INC',
u'INGERSOLL RAND CO',
u'INGERSOLL RAND CO LTD',
u'INGERSOLL RAND PLC',
u'INNOSPEC INC',
u'INTERMUNE INC',
u'INTERMUNE PHARMACEUTICALS',
u'J B OXFORD HOLDINGS INC',
u'J P MORGAN CHASE & CO',
u'JAZZ PHARMACEUTICALS INC',
u'JAZZ PHARMACEUTICALS PLC',
u'JOHNSON & JOHNSON',
u'JOHNSON CONTROLS INC',
u'JPMORGAN CHASE & CO',
u'K B R INC',
u'KINDER MORGAN INC',
u'KROGER COMPANY',
u'LAS VEGAS SANDS CORP',
u'LAWSON PRODUCTS INC',
u'M C I WORLDCOM INC',
u'M T S SYSTEMS CORP',
u'MASSEY ENERGY CO',
u'MAXIMUS INC',
u'MAXWELL LABORATORIES INC',
u'MAXWELL TECHNOLOGIES INC',
u'MELLON BANK CORP',
u'MELLON FINANCIAL CORP',
u'MELVILLE CORP',
u'MERCK & CO INC',
u'MERCK & CO INC NEW',
u'MERCK SERONO S A',
u'MERRILL LYNCH & CO INC',
u'METLIFE INC',
u'MICRUS ENDOVASCULAR CORP',
u'MIRANT CORP',
u'MIRANT CORP NEW',
u'MONEYGRAM INTERNATIONAL INC',
u'MONSANTO CO NEW',
u'MORGAN J P & CO INC',
u'N C S HEALTHCARE INC',
u'N W A INC',
u'NASH FINCH COMPANY',
u'NEUROMETRIX INC',
u'NOBLE CORP',
u'NOBLE CORP BAAR',
u'NOBLE CORP PLC',
u'NORTHWEST AIRLINES CORP',
u'O M I CORP NEW',
u'P N C BANK CORP',
u'P N C FINANCIAL CORP',
u'P N C FINANCIAL SERVICES GRP INC',
u'P P G INDUSTRIES INC',
u'PAMRAPO BANCORP INC',
u'PARKER DRILLING CO',
u'PFIZER INC',
u'PHARMACIA & UPJOHN INC',
u'PHELPS DODGE CORP',
u'PILGRIMS PRIDE CORP NEW',
u'PLUM CREEK TIMBER CO INC',
u'PLUM CREEK TIMBER CO L P',
u'POLO RALPH LAUREN CORP',
u'PRIDE INTERNATIONAL INC DEL',
u'PRUDENTIAL FINANCIAL INC',
u'QUEST DIAGNOSTICS INC',
u'R R I ENERGY INC',
u'RAE SYSTEMS INC',
u'RALPH LAUREN CORP',
u'RELIANT ENERGY INC',
u'RELIANT RESOURCES INC',
u'REPUBLIC SERVICES INC',
u'RHONE POULENC S A',
u'RIGGS NATIONAL CORP WASH D C',
u'ROWAN COMPANIES INC',
u'ROWAN COMPANIES PLC',
u'SALOMON INC',
u'SARA LEE CORP',
u'SCHERING PLOUGH CORP',
u'SCHLUMBERGER LTD',
u'SCHNITZER STEEL INDUSTRIES INC',
u'SCIENCE APPLICATIONS INTL CORP',
u'SCOTTS COMPANY',
u'SCOTTS MIRACLE GRO CO',
u'SEARS HOLDINGS CORP',
u'SERONO S A',
u'SIRVA INC',
u'SONAT OFFSHORE DRILLING INC',
u'SPARTAN MOTORS INC',
u'SPECTRANETICS CORP',
u'STRYKER CORP',
u'SUPER MICRO COMPUTER INC',
u'SUPER VALU STORES INC',
u'SUPERVALU INC',
u'SYMBOL TECHNOLOGIES INC',
u'T R W AUTOMOTIVE HOLDINGS CORP',
u'TEAM INC',
u'TEXTRON INC',
u'THORN APPLE VALLEY INC',
u'TIDEWATER INC',
u'TITAN CORP',
u'TOMMY HILFIGER CORP',
u'TRANSOCEAN INC',
u'TRANSOCEAN INC NEW',
u'TRANSOCEAN LTD',
u'TRANSOCEAN OFFSHORE INC',
u'TRANSOCEAN SEDCO FOREX INC',
u'TRIBUNE COMPANY NEW',
u'TYCO INTERNATIONAL LTD',
u'TYCO INTERNATIONAL LTD BERMUDA',
u'TYCO INTERNATIONAL LTD NEW',
u'TYCO INTERNATIONAL LTD SWTZLND',
u'TYCO LABS INC',
u'TYSON FOODS INC',
u'U S ECOLOGY INC',
u'UNIONBANCAL CORP',
u'UNITED PARCEL SERVICE INC',
u'UNITED TECHNOLOGIES CORP',
u'UNIVERSAL CORPORATION',
u'UNIVERSAL LEAF TOB INC',
u'UTSTARCOM HOLDINGS CORP',
u'UTSTARCOM INC',
u'WEATHERFORD INTL INC NEW',
u'WEATHERFORD INTL LTD',
u'WEATHERFORD INTL LTD NEW',
u'WELLCARE GROUP INC',
u'WELLCARE HEALTH PLANS INC',
u'WELLS FARGO & CO NEW',
u'WILLBROS GROUP INC',
u'WILLBROS GROUP INC DEL',
u'WORLDCOM INC GA',
u'WORLDCOM INC GA NEW',
u'WRIGHT MEDICAL GROUP INC',
u'ZIMMER HOLDINGS INC'}
without_bad_copy=copy.deepcopy(cleaned_data)
cleaned_data.columns
Index([u'PERMNO', u'Names Date', u'Names Ending Date', u'Share Code', u'Exchange Code', u'Standard Industrial Classification Code', u'CUSIP', u'Ticker Symbol', u'Company Name', u'Share Class', u'Trading Symbol', u'North American Industry Classification System', u'Primary Exchange', u'Trading Status', u'Security Status', u'PERMCO', u'Nasdaq Issue Number', u'Exchange Code Header', u'Standard Industrial Classification Code.1', u'CUSIP Header', u'Declaration Date', u'Amount After Delisting', u'Date of Delisting Payment', u'Delisting Code', u'Date of Next Available Information', u'Payment Date', u'Record Date', u'Number of Shares Outstanding', u'Header SIC Major Gorup', u'Header SIC Industry Group', u'Distribution Code', u'Dividend Cash Amount', u'Factor to Adjust Price', u'Factor to Adjust Shares', u'Acquiring PERMNO', u'Acquiring PERMCO', u'New PERMNO', u'Delisting Return Without Dividends', u'Delisting Price', u'Delisting Return', u'Nasdaq Traits Code', u'Nasdaq National Market Indicator', u'Market Maker Count', u'Nasd Index Code', u'Bid or Low Price', u'Ask or High Price', u'Price or Bid/Ask Average', u'Volume', u'Returns', u'Bid', u'Ask', u'Shares Outstanding', u'Cumulative Factor to Adjust Prices', u'Cumulative Factor to Adjust Shares/Vol', u'Price Alternate', u'NASDAQ Number of Trades', u'Returns without Dividends', u'Value-Weighted Return-incl. dividends', u'Value-Weighted Return-excl. dividends', u'Equal-Weighted Return-incl. dividends', u'Equal-Weighted Return-excl. dividends', u'Return on the S&P 500 Index', u'Dates'], dtype='object')
del cleaned_data['Return on the S&P 500 Index']
cleaned_data.columns
Index([u'PERMNO', u'Names Date', u'Names Ending Date', u'Share Code', u'Exchange Code', u'Standard Industrial Classification Code', u'CUSIP', u'Ticker Symbol', u'Company Name', u'Share Class', u'Trading Symbol', u'North American Industry Classification System', u'Primary Exchange', u'Trading Status', u'Security Status', u'PERMCO', u'Nasdaq Issue Number', u'Exchange Code Header', u'Standard Industrial Classification Code.1', u'CUSIP Header', u'Declaration Date', u'Amount After Delisting', u'Date of Delisting Payment', u'Delisting Code', u'Date of Next Available Information', u'Payment Date', u'Record Date', u'Number of Shares Outstanding', u'Header SIC Major Gorup', u'Header SIC Industry Group', u'Distribution Code', u'Dividend Cash Amount', u'Factor to Adjust Price', u'Factor to Adjust Shares', u'Acquiring PERMNO', u'Acquiring PERMCO', u'New PERMNO', u'Delisting Return Without Dividends', u'Delisting Price', u'Delisting Return', u'Nasdaq Traits Code', u'Nasdaq National Market Indicator', u'Market Maker Count', u'Nasd Index Code', u'Bid or Low Price', u'Ask or High Price', u'Price or Bid/Ask Average', u'Volume', u'Returns', u'Bid', u'Ask', u'Shares Outstanding', u'Cumulative Factor to Adjust Prices', u'Cumulative Factor to Adjust Shares/Vol', u'Price Alternate', u'NASDAQ Number of Trades', u'Returns without Dividends', u'Value-Weighted Return-incl. dividends', u'Value-Weighted Return-excl. dividends', u'Equal-Weighted Return-incl. dividends', u'Equal-Weighted Return-excl. dividends', u'Dates'], dtype='object')
#del cleaned_data['Nasdaq Issue Number']
#del cleaned_data['Exchange Code Header']
#del cleaned_data['Factor to Adjust Shares']
#del cleaned_data['Factor to Adjust Price']
#del cleaned_data['Nasdaq Traits Code']
#del cleaned_data['Returns']
#del cleaned_data['Returns without Dividends']
#del cleaned_data['Value-Weighted Return-incl. dividends']
del cleaned_data['Equal-Weighted Return-excl. dividends']
del cleaned_data['Trading Symbol']
cleaned_data.columns
Index([u'PERMNO', u'Names Date', u'Names Ending Date', u'Share Code', u'Exchange Code', u'Standard Industrial Classification Code', u'CUSIP', u'Ticker Symbol', u'Company Name', u'Share Class', u'North American Industry Classification System', u'Primary Exchange', u'Trading Status', u'Security Status', u'PERMCO', u'Standard Industrial Classification Code.1', u'CUSIP Header', u'Declaration Date', u'Amount After Delisting', u'Date of Delisting Payment', u'Delisting Code', u'Date of Next Available Information', u'Payment Date', u'Record Date', u'Number of Shares Outstanding', u'Header SIC Major Gorup', u'Header SIC Industry Group', u'Distribution Code', u'Dividend Cash Amount', u'Acquiring PERMNO', u'Acquiring PERMCO', u'New PERMNO', u'Delisting Return Without Dividends', u'Delisting Price', u'Delisting Return', u'Nasdaq National Market Indicator', u'Market Maker Count', u'Nasd Index Code', u'Bid or Low Price', u'Ask or High Price', u'Price or Bid/Ask Average', u'Volume', u'Bid', u'Ask', u'Shares Outstanding', u'Cumulative Factor to Adjust Prices', u'Cumulative Factor to Adjust Shares/Vol', u'Price Alternate', u'NASDAQ Number of Trades', u'Value-Weighted Return-excl. dividends', u'Equal-Weighted Return-incl. dividends', u'Dates'], dtype='object')
# del cleaned_data['Equal-Weighted Return-incl. dividends']
# del cleaned_data['Market Maker Count']
# del cleaned_data['Nasd Index Code']
# del cleaned_data['Primary Exchange']
for c in cleaned_data.columns:
print 'Do you want to delete ', c
resp = raw_input()
if resp =='yes':
del cleaned_data[c]
Do you want to delete PERMNO yes Do you want to delete Names Date no Do you want to delete Names Ending Date yes Do you want to delete Share Code yes Do you want to delete Exchange Code yes Do you want to delete Standard Industrial Classification Code no Do you want to delete CUSIP yes Do you want to delete Ticker Symbol no Do you want to delete Company Name no Do you want to delete Share Class yes Do you want to delete North American Industry Classification System no Do you want to delete Trading Status no Do you want to delete Security Status yes Do you want to delete PERMCO yes Do you want to delete Standard Industrial Classification Code.1 no Do you want to delete CUSIP Header yes Do you want to delete Declaration Date no Do you want to delete Amount After Delisting no Do you want to delete Date of Delisting Payment no Do you want to delete Delisting Code no Do you want to delete Date of Next Available Information no Do you want to delete Payment Date no Do you want to delete Record Date no Do you want to delete Number of Shares Outstanding yes Do you want to delete Header SIC Major Gorup yes Do you want to delete Header SIC Industry Group yes Do you want to delete Distribution Code yes Do you want to delete Dividend Cash Amount no Do you want to delete Acquiring PERMNO yes Do you want to delete Acquiring PERMCO yes Do you want to delete New PERMNO yes Do you want to delete Delisting Return Without Dividends no Do you want to delete Delisting Price no Do you want to delete Delisting Return no Do you want to delete Nasdaq National Market Indicator yes Do you want to delete Bid or Low Price no Do you want to delete Ask or High Price no Do you want to delete Price or Bid/Ask Average no Do you want to delete Volume no Do you want to delete Bid no Do you want to delete Ask no Do you want to delete Shares Outstanding yes Do you want to delete Cumulative Factor to Adjust Prices yes Do you want to delete Cumulative Factor to Adjust Shares/Vol yes Do you want to delete Price Alternate yes Do you want to delete NASDAQ Number of Trades yes Do you want to delete Value-Weighted Return-excl. dividends yes Do you want to delete Dates no
cleaned_data.to_csv('data_trimmed.csv')
ls -sh
total 337M 12K Bad_Names_Sheet_2.xlsx 90M data_trimmed.csv 248M Stock_Data.xlsx 20K companies_to_trim.xlsx 16K sheet_2_again.xlsx 112K Untitled0.ipynb
dta_for_dates = pandas.read_csv('data_trimmed.csv')
dta1 = dta_for_dates
dta1.sort(columns = 'Company Name')
| Company Name | Names Date | Standard Industrial Classification Code | Ticker Symbol | Company Name.1 | North American Industry Classification System | Trading Status | Standard Industrial Classification Code.1 | Declaration Date | Amount After Delisting | Date of Delisting Payment | Delisting Code | Date of Next Available Information | Payment Date | Record Date | Dividend Cash Amount | Delisting Return Without Dividends | Delisting Price | Delisting Return | Bid or Low Price | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 23519 | NaN | 19880421 | NaN | NaN | NaN | NaN | NaN | 3810 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 24798 | NaN | 19870901 | NaN | NaN | NaN | NaN | NaN | 6210 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 38009 | NaN | 19880826 | NaN | NaN | NaN | NaN | NaN | 1389 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 38010 | NaN | 19880829 | NaN | NaN | NaN | NaN | NaN | 1389 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 38011 | NaN | 19880830 | NaN | NaN | NaN | NaN | NaN | 1389 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 38012 | NaN | 19880902 | NaN | NaN | NaN | NaN | NaN | 1389 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 40459 | NaN | 19880727 | NaN | NaN | NaN | NaN | NaN | 2834 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 469247 | NaN | 19890503 | NaN | NaN | NaN | NaN | NaN | 7370 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 469248 | NaN | 19890504 | NaN | NaN | NaN | NaN | NaN | 7370 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 469465 | NaN | 19891110 | NaN | NaN | NaN | NaN | NaN | 6036 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 469466 | NaN | 19891113 | NaN | NaN | NaN | NaN | NaN | 6036 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 474670 | NaN | 19890713 | NaN | NaN | NaN | NaN | NaN | 8050 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 474671 | NaN | 19890929 | NaN | NaN | NaN | NaN | NaN | 6099 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 480945 | NaN | 19910411 | NaN | NaN | NaN | NaN | NaN | 6324 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 486505 | NaN | 19910423 | NaN | NaN | NaN | NaN | NaN | 2830 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 486506 | NaN | 19910424 | NaN | NaN | NaN | NaN | NaN | 2830 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 491670 | NaN | 19920106 | NaN | NaN | NaN | NaN | NaN | 3840 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 491671 | NaN | 19920120 | NaN | NaN | NaN | NaN | NaN | 3840 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 497202 | NaN | 19920109 | NaN | NaN | NaN | NaN | NaN | 2879 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 497203 | NaN | 19920110 | NaN | NaN | NaN | NaN | NaN | 2879 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 497204 | NaN | 19920129 | NaN | NaN | NaN | NaN | NaN | 2879 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 497205 | NaN | 19920130 | NaN | NaN | NaN | NaN | NaN | 2879 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 503226 | NaN | 19920302 | NaN | NaN | NaN | NaN | NaN | 7812 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 503227 | NaN | 19920317 | NaN | NaN | NaN | NaN | NaN | 7812 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 503228 | NaN | 19920318 | NaN | NaN | NaN | NaN | NaN | 7812 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 506148 | NaN | 19920401 | NaN | NaN | NaN | NaN | NaN | 3523 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 506149 | NaN | 19920402 | NaN | NaN | NaN | NaN | NaN | 3523 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 506150 | NaN | 19920601 | NaN | NaN | NaN | NaN | NaN | 8093 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 506151 | NaN | 19920608 | NaN | NaN | NaN | NaN | NaN | 8093 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 521828 | NaN | 19921130 | NaN | NaN | NaN | NaN | NaN | 3589 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 521829 | NaN | 19920401 | NaN | NaN | NaN | NaN | NaN | 3840 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 521830 | NaN | 19920422 | NaN | NaN | NaN | NaN | NaN | 3840 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 521831 | NaN | 19920423 | NaN | NaN | NaN | NaN | NaN | 3840 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 529357 | NaN | 19931026 | NaN | NaN | NaN | NaN | NaN | 7371 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 529358 | NaN | 19931001 | NaN | NaN | NaN | NaN | NaN | 5944 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 529359 | NaN | 19931012 | NaN | NaN | NaN | NaN | NaN | 5944 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 529360 | NaN | 19931013 | NaN | NaN | NaN | NaN | NaN | 5944 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 532021 | NaN | 19931101 | NaN | NaN | NaN | NaN | NaN | 3310 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 532022 | NaN | 19931112 | NaN | NaN | NaN | NaN | NaN | 3310 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 532023 | NaN | 19931115 | NaN | NaN | NaN | NaN | NaN | 3310 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | ... |
| 672319 | A G A MEDICAL HOLDINGS INC | 20091021 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 14.31 | ... |
| 672320 | A G A MEDICAL HOLDINGS INC | 20091022 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 14.41 | ... |
| 672321 | A G A MEDICAL HOLDINGS INC | 20091023 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 14.49 | ... |
| 672322 | A G A MEDICAL HOLDINGS INC | 20091026 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 14.24 | ... |
| 672323 | A G A MEDICAL HOLDINGS INC | 20091027 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.60 | ... |
| 672324 | A G A MEDICAL HOLDINGS INC | 20091028 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.25 | ... |
| 672325 | A G A MEDICAL HOLDINGS INC | 20091029 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.25 | ... |
| 672326 | A G A MEDICAL HOLDINGS INC | 20091030 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.28 | ... |
| 672327 | A G A MEDICAL HOLDINGS INC | 20091102 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.30 | ... |
| 672328 | A G A MEDICAL HOLDINGS INC | 20091103 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.28 | ... |
| 672329 | A G A MEDICAL HOLDINGS INC | 20091104 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.30 | ... |
| 672330 | A G A MEDICAL HOLDINGS INC | 20091105 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.48 | ... |
| 672331 | A G A MEDICAL HOLDINGS INC | 20091106 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.50 | ... |
| 672332 | A G A MEDICAL HOLDINGS INC | 20091109 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.58 | ... |
| 672333 | A G A MEDICAL HOLDINGS INC | 20091110 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.48 | ... |
| 672334 | A G A MEDICAL HOLDINGS INC | 20091111 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 13.25 | ... |
| 672335 | A G A MEDICAL HOLDINGS INC | 20091112 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 12.51 | ... |
| 672336 | A G A MEDICAL HOLDINGS INC | 20091113 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 12.45 | ... |
| 672337 | A G A MEDICAL HOLDINGS INC | 20091116 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 11.91 | ... |
| 672338 | A G A MEDICAL HOLDINGS INC | 20091117 | 9999 | AGAM | A G A MEDICAL HOLDINGS INC | 334510 | A | 9999 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 12.17 | ... |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
674623 rows × 26 columns
for i in range(len(list(set(dta1['Company Name'])))):
print sort(list(set(dta1['Company Name'])))[i]
A G A MEDICAL HOLDINGS INC A O L INC A O L TIME WARNER INC ABBOTT LABORATORIES ADA ES INC AETNA INC AETNA INC NEW AETNA LIFE & CASUALTY CO ALLERGAN INC ALLIANCE ONE INTERNATIONAL INC ALPHA NATURAL RESOURCES INC AMERICA ONLINE INC DEL AMERICAN ECOLOGY CORP AMERICAN ELECTRIC POWER CO INC AMERICAN EXPRESS CO AMERICAN INTERNATIONAL GROUP INC AMGEN INC AMSOUTH BANCORPORATION AON PLC AQUA ALLIANCE INC ARAMARK HOLDINGS CORP ARCHER DANIELS MIDLAND CO ARTHROCARE CORP ASHLAND INC ASHLAND INC NEW ASHLAND OIL INC AURORA FOODS INC BAKER HUGHES INC BANCO POPULAR DE P R BANK NEW YORK INC BANK OF NEW YORK MELLON CORP BARD C R INC BEAZER HOMES USA INC BIOMET INC BRISTOL MYERS CO BRISTOL MYERS SQUIBB CO C A INC C V S CAREMARK CORP C V S CORP CARNIVAL CORP CARNIVAL CRUISE LINES INC CEPHALON INC CHEMTURA CORP CHEVRON CORP NEW CHEVRONTEXACO CORP CHIQUITA BRANDS INTL INC COMMUNITYONE BANCORP COMPUTER ASSOCIATES INTL INC COMVERSE TECHNOLOGY INC CONAGRA INC COVENTRY CORP COVENTRY HEALTH CARE INC CROMPTON & KNOWLES CORP DIEBOLD INC DR PEPPER SEVEN UP CO INC DR PEPPER SNAPPLE GROUP INC ELAN CORP PLC ELECTRONIC CLEARING HOUSE INC ENDO HEALTH SOLUTIONS INC ENDO PHARMACEUTICALS HLDNGS INC ENDOTRONICS INC ENERGY PARTNERS LTD EXACTECH INC EXPRESS SCRIPTS HOLDING CO EXPRESS SCRIPTS INC EXXON MOBIL CORP FALCONSTOR SOFTWARE INC FARO TECHNOLOGIES INC FIDELITY NATIONAL FINANCIAL INC FIDELITY NATIONAL FINL INC NEW FLOWSERVE CORP FOREST LABS INC FRIEDMANS INC GENERAL ELECTRIC CO GOOGLE INC GUIDANT CORP H C A HEALTHCARE CO H C A HOLDINGS INC H C A HOSPITAL CORP AMERICA H C A INC NEW HEALTHSOUTH CORP HELMERICH & PAYNE INC HERLEY INDUSTRIES INC HERLEY MICROWAVE SYSTEMS INC HONEYWELL INC HONEYWELL INTERNATIONAL INC HOSPITAL CORP AMERICA I G I LABORATORIES INC IMPERIAL HOLDINGS INC ING U S INC INGERSOLL RAND CO INGERSOLL RAND CO LTD INGERSOLL RAND PLC INNOSPEC INC INTERMUNE INC INTERMUNE PHARMACEUTICALS J B OXFORD HOLDINGS INC J P MORGAN CHASE & CO JAZZ PHARMACEUTICALS INC JAZZ PHARMACEUTICALS PLC JOHNSON & JOHNSON JOHNSON CONTROLS INC JPMORGAN CHASE & CO K B R INC KINDER MORGAN INC KROGER COMPANY LAS VEGAS SANDS CORP LAWSON PRODUCTS INC M C I WORLDCOM INC M T S SYSTEMS CORP MASSEY ENERGY CO MAXIMUS INC MAXWELL LABORATORIES INC MAXWELL TECHNOLOGIES INC MELLON BANK CORP MELLON FINANCIAL CORP MELVILLE CORP MERCK & CO INC MERCK & CO INC NEW MERCK SERONO S A MERRILL LYNCH & CO INC METLIFE INC MICRUS ENDOVASCULAR CORP MIRANT CORP MIRANT CORP NEW MONEYGRAM INTERNATIONAL INC MONSANTO CO NEW MORGAN J P & CO INC N C S HEALTHCARE INC N W A INC NASH FINCH COMPANY NEUROMETRIX INC NOBLE CORP NOBLE CORP BAAR NOBLE CORP PLC NORTHWEST AIRLINES CORP O M I CORP NEW P N C BANK CORP P N C FINANCIAL CORP P N C FINANCIAL SERVICES GRP INC P P G INDUSTRIES INC PAMRAPO BANCORP INC PARKER DRILLING CO PFIZER INC PHARMACIA & UPJOHN INC PHELPS DODGE CORP PILGRIMS PRIDE CORP NEW PLUM CREEK TIMBER CO INC PLUM CREEK TIMBER CO L P POLO RALPH LAUREN CORP PRIDE INTERNATIONAL INC DEL PRUDENTIAL FINANCIAL INC QUEST DIAGNOSTICS INC R R I ENERGY INC RAE SYSTEMS INC RALPH LAUREN CORP RELIANT ENERGY INC RELIANT RESOURCES INC REPUBLIC SERVICES INC RHONE POULENC S A RIGGS NATIONAL CORP WASH D C ROWAN COMPANIES INC ROWAN COMPANIES PLC SALOMON INC SARA LEE CORP SCHERING PLOUGH CORP SCHLUMBERGER LTD SCHNITZER STEEL INDUSTRIES INC SCIENCE APPLICATIONS INTL CORP SCOTTS COMPANY SCOTTS MIRACLE GRO CO SEARS HOLDINGS CORP SERONO S A SIRVA INC SONAT OFFSHORE DRILLING INC SPARTAN MOTORS INC SPECTRANETICS CORP STRYKER CORP SUPER MICRO COMPUTER INC SUPER VALU STORES INC SUPERVALU INC SYMBOL TECHNOLOGIES INC T R W AUTOMOTIVE HOLDINGS CORP TEAM INC TEXTRON INC THORN APPLE VALLEY INC TIDEWATER INC TITAN CORP TOMMY HILFIGER CORP TRANSOCEAN INC TRANSOCEAN INC NEW TRANSOCEAN LTD TRANSOCEAN OFFSHORE INC TRANSOCEAN SEDCO FOREX INC TRIBUNE COMPANY NEW TYCO INTERNATIONAL LTD TYCO INTERNATIONAL LTD BERMUDA TYCO INTERNATIONAL LTD NEW TYCO INTERNATIONAL LTD SWTZLND TYCO LABS INC TYSON FOODS INC U S ECOLOGY INC UNIONBANCAL CORP UNITED PARCEL SERVICE INC UNITED TECHNOLOGIES CORP UNIVERSAL CORPORATION UNIVERSAL LEAF TOB INC UTSTARCOM HOLDINGS CORP UTSTARCOM INC WEATHERFORD INTL INC NEW WEATHERFORD INTL LTD WEATHERFORD INTL LTD NEW WELLCARE GROUP INC WELLCARE HEALTH PLANS INC WELLS FARGO & CO NEW WILLBROS GROUP INC WILLBROS GROUP INC DEL WORLDCOM INC GA WORLDCOM INC GA NEW WRIGHT MEDICAL GROUP INC ZIMMER HOLDINGS INC nan
orig = pandas.read_excel('Stock_Data.xlsx', 'Original')
tickers = list(set(dta1['Ticker Symbol']))
orig
| Company | Exchange | Ticker | Based | Date | |
|---|---|---|---|---|---|
| 0 | AB Volvo | OM | VOLV B | Foreign | 2008-03-01 |
| 1 | ABB Inc. | SWX | ABBN | Foreign | 2010-09-29 |
| 2 | ABB Ltd. | SWX | ABBN | Foreign | 2010-10-10 |
| 3 | ABB Middle East & Africa Participations AG | SWX | ABBN | Foreign | 2001-04-11 |
| 4 | Abbott Laboratories | NYSE | ABT | Domestic | 2012-05-07 |
| 5 | ADA-ES | NASDAQ | ADES | Domestic | 2013-09-12 |
| 6 | Adelphia Communications | NASDAQ | ADELQ | Domestic | 2005-05-01 |
| 7 | AEP Energy Services | NYSE | AEP | Domestic | 2005-01-01 |
| 8 | Aetna | NYSE | AET | Domestic | 1993-08-01 |
| 9 | AGA Medical | NASDAQ | AGAM | Domestic | 2008-06-01 |
| 10 | AGCO Corporation | NYSE | AGCO | Foreign | 2009-09-29 |
| 11 | Aibel Group Ltd. | SWX | ABB | Foreign | 2008-11-21 |
| 12 | Air France / KLM | ENXTRA | AF | Foreign | 2008-07-22 |
| 13 | Akzo Nobel | ENXTAM | AKZA | Foreign | 2007-12-01 |
| 14 | Akzo Nobel Chemicals International | ENXTAM | AKZA | Foreign | 2006-05-17 |
| 15 | Alcatel-Lucent | ENXTAM | ALU | Foreign | 2010-12-20 |
| 16 | Allergan | NYSE | AGN | Domestic | 2010-09-01 |
| 17 | Alliance One International A.B. | NYSE | AOI | Domestic | 2010-08-06 |
| 18 | Alpha Technologies Inc. | OTCPK | AHAG | Domestic | 2004-04-13 |
| 19 | American Business Corp. | OTCPK | ARBU | Domestic | 2007-01-24 |
| 20 | American Ecology Recycle Center | NASDAQ | ECOL | Domestic | 2002-08-08 |
| 21 | American Express Bank Int'l | NYSE | AXP | Domestic | 2007-08-06 |
| 22 | American Int'l Group | NYSE | AIG | Domestic | 2004-11-01 |
| 23 | American Int'l Group | NYSE | AIG | Domestic | 2006-02-07 |
| 24 | American Italian Pasta Company | NASDAQ | AIPC | Domestic | 2008-09-15 |
| 25 | Amgen | NASDAQ | AMGN | Domestic | 2012-12-19 |
| 26 | AmSouth Bancorp | NYSE | ASO | Domestic | 2004-10-01 |
| 27 | AOL | NYSE | AOL | Domestic | 2004-12-01 |
| 28 | AON Corp. | NYSE | AON | Foreign | 2011-12-20 |
| 29 | Aqua Alliance, Inc. | AMEX | AAI | Domestic | 2001-06-22 |
| 30 | Aracoma Coal Co. | NYSE | MEE | Domestic | 2009-01-14 |
| 31 | Archer Daniels Midland Company | NYSE | ADM | Domestic | 2008-03-06 |
| 32 | Archer Daniels Midland Company | NYSE | ADM | Domestic | 2013-12-20 |
| 33 | Archer Daniels Midland Company | NYSE | ADM | Domestic | 1996-10-15 |
| 34 | Arthrocare Corp. | NASDAQ | ARTC | Domestic | 2014-01-07 |
| 35 | Ashland Inc. | NYSE | ASH | Domestic | 2002-05-13 |
| 36 | Aurora Foods | NYSE | AOR | Domestic | 2001-01-01 |
| 37 | BAE Systems PLC | LSE | BA. | Foreign | 2010-03-01 |
| 38 | Baker Hughes | NYSE | BHI | Domestic | 2007-04-01 |
| 39 | Baker Hughes Services Int'l | NYSE | BHI | Domestic | 2007-04-11 |
| 40 | Banco Popular De Puerto Rico | NASDAQ | BPOP | Domestic | 2003-01-01 |
| 41 | Bank of New York | NYSE | BK | Domestic | 2005-11-01 |
| 42 | BankAtlantic | NYSE | BBX | Domestic | 2006-03-01 |
| 43 | Barclays Bank | LSE | BARC | Foreign | 2010-08-01 |
| 44 | Barclays Bank | LSE | BARC | Foreign | 2012-06-27 |
| 45 | BASF | DB | BAS | Foreign | 1999-05-20 |
| 46 | Bayer AG | DB | BAYN | Foreign | 2004-12-09 |
| 47 | Bayer Corporation | DB | BAYN | Foreign | 2005-05-24 |
| 48 | Beazer Homes USA, Inc. | NYSE | BZH | Domestic | 2009-07-01 |
| 49 | Bennett Environmental, Inc. | TSXV | BEV.H | Foreign | 2008-07-31 |
| 50 | Bilfinger SE | DB | GBF | Foreign | 2013-12-11 |
| 51 | Biomet | NASDAQ | BMET | Domestic | 2007-09-01 |
| 52 | Biovail | TSE | BVF | Foreign | 2008-05-16 |
| 53 | Boeing Co. | NYSE | BARC | Domestic | 2006-06-01 |
| 54 | BP | LSE | BP | Foreign | 2007-10-25 |
| 55 | BP | LSE | BP | Foreign | 2012-11-15 |
| 56 | BP Products North America | LSE | BP | Foreign | 2009-03-12 |
| 57 | Bristol Myers Squibb Co. | NYSE | BMY | Domestic | 2003-05-29 |
| 58 | Bristol-Myers Squibb | NYSE | BMY | Domestic | 2005-06-01 |
| 59 | Bristol-Myers Squibb | NYSE | BMY | Domestic | 2007-06-11 |
| ... | ... | ... | ... | ... |
318 rows × 5 columns
Below is a list of tickers and dates. The tickers that just have a [] next to them do not have a date of incident. The way I got this is that I
In other words, there are tickers in the sheet that she sent us that are not in your original sheet. Can you please investigate and update me accordingly?
ticker_data = []
for t in tickers:
ix = (orig['Ticker'] == t).values
ticker_data.append((t, orig['Date'].values[ix]))
print t, orig['Date'].values[ix]
nan [] AGN ['2010-08-31T20:00:00.000000000-0400'] AXP ['2007-08-05T20:00:00.000000000-0400'] SIR ['2006-01-31T19:00:00.000000000-0500'] ECOL ['2002-08-07T20:00:00.000000000-0400'] BK ['2005-10-31T19:00:00.000000000-0500'] FNF ['2013-02-14T19:00:00.000000000-0500'] EPL ['2008-02-18T19:00:00.000000000-0500'] FALC ['2012-06-26T20:00:00.000000000-0400'] IOSP ['2010-03-17T20:00:00.000000000-0400'] MIR ['2007-06-30T20:00:00.000000000-0400'] SHLD ['2001-03-31T19:00:00.000000000-0500'] CMVT ['2011-04-06T20:00:00.000000000-0400'] FRX ['2010-09-14T20:00:00.000000000-0400'] RP ['2003-09-17T20:00:00.000000000-0400'] AON ['2011-12-19T19:00:00.000000000-0500'] AOL ['2004-11-30T19:00:00.000000000-0500'] AOI ['2010-08-05T20:00:00.000000000-0400'] IOP [] PRU ['2008-07-31T20:00:00.000000000-0400' '1994-09-30T20:00:00.000000000-0400'] HRC [] RL ['2013-04-21T20:00:00.000000000-0400'] RIG ['2010-10-31T20:00:00.000000000-0400'] CHMT ['2004-03-14T19:00:00.000000000-0500'] VOYA ['2012-06-11T20:00:00.000000000-0400'] ENDP ['2014-02-20T19:00:00.000000000-0500'] PD ['2004-08-08T20:00:00.000000000-0400'] CQB ['2007-03-05T19:00:00.000000000-0500'] GE ['2004-12-02T19:00:00.000000000-0500' '2011-12-22T19:00:00.000000000-0500'] MRK ['2011-11-06T19:00:00.000000000-0500'] SVU ['2010-08-31T20:00:00.000000000-0400'] JBOH ['2000-02-13T19:00:00.000000000-0500'] ENDO ['2006-07-17T20:00:00.000000000-0400'] UTSI ['2009-12-30T19:00:00.000000000-0500'] WG ['2008-04-30T20:00:00.000000000-0400'] CCL ['2002-04-18T20:00:00.000000000-0400' '2007-04-12T20:00:00.000000000-0400'] SYK ['2005-09-30T20:00:00.000000000-0400'] PNCF [] ITMN ['2006-09-30T20:00:00.000000000-0400'] DBD ['2013-10-11T20:00:00.000000000-0400'] TYC ['2012-09-23T20:00:00.000000000-0400'] UNBC [] TISI ['2012-11-14T19:00:00.000000000-0500'] MEND ['2005-01-31T19:00:00.000000000-0500'] JNJ ['2011-01-13T19:00:00.000000000-0500'] IFT ['2012-04-29T20:00:00.000000000-0400'] AEP ['2004-12-31T19:00:00.000000000-0500'] AET ['1993-07-31T20:00:00.000000000-0400'] CVTY [] WFC ['2010-02-28T19:00:00.000000000-0500' '2011-12-07T19:00:00.000000000-0500'] FLS ['2008-01-31T19:00:00.000000000-0500'] ESRX ['2007-08-31T20:00:00.000000000-0400'] WMGI ['2010-09-30T20:00:00.000000000-0400'] SMBL [] OMM ['2004-01-20T19:00:00.000000000-0500'] PCL ['2003-09-16T20:00:00.000000000-0400'] WCOM ['2005-08-31T20:00:00.000000000-0400'] MET ['2010-04-14T20:00:00.000000000-0400'] CEM [] UVV ['2010-07-31T20:00:00.000000000-0400' '2010-08-05T20:00:00.000000000-0400'] TTN ['2005-02-28T19:00:00.000000000-0500'] PKD ['2013-04-15T20:00:00.000000000-0400'] KBR ['2009-02-10T19:00:00.000000000-0500'] BMY ['2003-05-28T20:00:00.000000000-0400' '2005-05-31T20:00:00.000000000-0400' '2007-06-10T20:00:00.000000000-0400'] BMET ['2007-08-31T20:00:00.000000000-0400'] JAZZ ['2007-06-30T20:00:00.000000000-0400' '2007-07-12T20:00:00.000000000-0400'] MES [] TRB ['2007-12-16T19:00:00.000000000-0500'] ECHO ['2007-02-28T19:00:00.000000000-0500'] SPAR ['2008-12-16T19:00:00.000000000-0500'] HCA ['2000-12-13T19:00:00.000000000-0500'] RIGS ['2005-01-26T19:00:00.000000000-0500'] UPS ['2013-03-08T19:00:00.000000000-0500'] TRW ['2012-07-26T20:00:00.000000000-0400'] STRY [] SAIC ['2012-03-07T19:00:00.000000000-0500'] MGI ['2012-11-08T19:00:00.000000000-0500'] HLS ['2006-04-30T20:00:00.000000000-0400'] TAVI ['2001-04-09T20:00:00.000000000-0400'] NWA ['2010-08-26T20:00:00.000000000-0400'] MON ['2004-12-31T19:00:00.000000000-0500'] ANR ['2002-12-15T19:00:00.000000000-0500' '2007-02-20T19:00:00.000000000-0500'] SCTT [] UB ['2007-08-31T20:00:00.000000000-0400'] TSN ['2011-01-31T19:00:00.000000000-0500' '2011-02-09T19:00:00.000000000-0500' '2003-06-24T20:00:00.000000000-0400'] NURO ['2009-01-25T19:00:00.000000000-0500'] AMGN ['2012-12-18T19:00:00.000000000-0500'] KMI ['2010-02-16T19:00:00.000000000-0500'] COB ['2011-04-26T20:00:00.000000000-0400'] SMG ['2012-09-09T20:00:00.000000000-0400'] TMI [] BCR ['2013-05-12T20:00:00.000000000-0400'] FRDM [] RRI ['2007-02-28T19:00:00.000000000-0500'] HRLY ['2008-05-04T20:00:00.000000000-0400'] NE ['2010-10-31T20:00:00.000000000-0400'] AGAM ['2008-05-31T20:00:00.000000000-0400'] CNK [] ARTC ['2014-01-06T19:00:00.000000000-0500'] ARMK ['2008-11-09T19:00:00.000000000-0500'] SCHN ['2006-09-30T20:00:00.000000000-0400'] DGX ['2009-04-14T20:00:00.000000000-0400'] ASH ['2002-05-12T20:00:00.000000000-0400'] ASO ['2004-09-30T20:00:00.000000000-0400'] HP ['2009-07-28T20:00:00.000000000-0400'] LAWS ['2008-07-31T20:00:00.000000000-0400'] ZMH ['2007-08-31T20:00:00.000000000-0400'] SRA ['2005-09-30T20:00:00.000000000-0400'] ELN ['2010-12-07T19:00:00.000000000-0500' '2006-12-06T19:00:00.000000000-0500'] CA ['2004-08-31T20:00:00.000000000-0400'] EXAC ['2010-12-07T19:00:00.000000000-0500'] FRM ['2005-10-31T19:00:00.000000000-0500'] CEPH ['2008-09-25T20:00:00.000000000-0400'] RDC ['2007-10-08T20:00:00.000000000-0400'] PFE ['2007-02-28T19:00:00.000000000-0500' '2012-08-06T20:00:00.000000000-0400' '2004-05-12T20:00:00.000000000-0400'] PDE ['2010-11-03T20:00:00.000000000-0400'] ADM ['2008-03-05T19:00:00.000000000-0500' '2013-12-19T19:00:00.000000000-0500' '1996-10-14T20:00:00.000000000-0400'] MEE ['2009-01-13T19:00:00.000000000-0500'] GDT ['2003-06-11T20:00:00.000000000-0400' '2010-04-05T20:00:00.000000000-0400'] MEL ['2006-07-31T20:00:00.000000000-0400'] TYSNA [] MMS ['2007-06-30T20:00:00.000000000-0400'] BZH ['2009-06-30T20:00:00.000000000-0400'] KR ['2006-06-30T20:00:00.000000000-0400'] MXWL ['2011-01-30T19:00:00.000000000-0500'] MER ['1995-09-30T20:00:00.000000000-0400' '2003-08-31T20:00:00.000000000-0400'] IG ['2002-03-27T19:00:00.000000000-0500'] SB ['1992-04-30T20:00:00.000000000-0400'] TDW ['2010-11-03T20:00:00.000000000-0400'] SBL ['2004-05-31T20:00:00.000000000-0400'] DPS ['2005-10-13T20:00:00.000000000-0400'] CVX ['2007-10-31T20:00:00.000000000-0400' '2010-07-25T20:00:00.000000000-0400' '2009-07-23T20:00:00.000000000-0400'] FRDMA [] FARO ['2008-05-31T20:00:00.000000000-0400'] AAI ['2001-06-21T20:00:00.000000000-0400'] CVS ['2010-10-12T20:00:00.000000000-0400'] NAFC ['2001-10-09T20:00:00.000000000-0400' '2004-08-12T20:00:00.000000000-0400'] CVH ['2012-11-18T19:00:00.000000000-0500'] TOM ['2005-07-31T20:00:00.000000000-0400'] SPNC ['2009-12-16T19:00:00.000000000-0500'] MTSC ['2008-03-11T20:00:00.000000000-0400'] AIG ['2004-10-31T19:00:00.000000000-0500' '2006-02-06T19:00:00.000000000-0500'] AMER [] CAG ['2005-08-31T20:00:00.000000000-0400'] PPC ['2009-12-29T19:00:00.000000000-0500'] PPG ['2010-12-20T19:00:00.000000000-0500'] ADES ['2013-09-11T20:00:00.000000000-0400'] SGP ['2006-08-23T20:00:00.000000000-0400' '2006-04-23T20:00:00.000000000-0400'] BPOP ['2002-12-31T19:00:00.000000000-0500'] WFT ['2013-11-25T19:00:00.000000000-0500'] TXT ['2007-07-31T20:00:00.000000000-0400'] GOOG ['2011-09-23T20:00:00.000000000-0400'] LVS ['2013-08-26T20:00:00.000000000-0400'] HON ['2007-09-12T20:00:00.000000000-0400'] IR ['2007-09-30T20:00:00.000000000-0400'] AOR ['2000-12-31T19:00:00.000000000-0500'] ABT ['2012-05-06T20:00:00.000000000-0400' '2010-12-06T19:00:00.000000000-0500'] WCG ['2009-05-04T20:00:00.000000000-0400'] SLE ['2001-06-21T20:00:00.000000000-0400'] UTX ['2012-06-27T20:00:00.000000000-0400'] XOM ['2009-08-11T20:00:00.000000000-0400' '2008-12-22T19:00:00.000000000-0500'] JCI ['2007-09-30T20:00:00.000000000-0400'] SLB ['2006-05-31T20:00:00.000000000-0400'] BHI ['2007-03-31T20:00:00.000000000-0400' '2007-04-10T20:00:00.000000000-0400'] SMCI ['2006-09-04T20:00:00.000000000-0400'] JPM ['2011-07-06T20:00:00.000000000-0400' '2014-01-06T19:00:00.000000000-0500' '2013-11-18T19:00:00.000000000-0500'] RSG ['2008-09-30T20:00:00.000000000-0400'] PNU ['2007-03-26T20:00:00.000000000-0400' '2009-08-30T20:00:00.000000000-0400' '2007-02-28T19:00:00.000000000-0500'] NCSS ['2001-12-11T19:00:00.000000000-0500'] RAE ['2010-12-09T19:00:00.000000000-0500'] PBCI ['2010-03-28T20:00:00.000000000-0400'] PNC ['2003-05-31T20:00:00.000000000-0400']
Take a look at these plots, keep track of oes that need to be investigate further. Also the one's that have a steep drop in a day are likely because there was a split, so that is actually normal. Instead, just look out for the ones that look waayyy wacky. If you can investigate (i.e. see what the stock looks like on yahoo finance and see if it matches) that would be great. If not, just keep a list of tickers and we will eventually drop them.
for t in tickers:
indiv_data = dta1[dta1['Ticker Symbol'] == t]
try:
fig = plt.figure()
title = indiv_data['Company Name'].values[0]
indiv_data.plot('Dates', 'Price or Bid/Ask Average', subplots=True, title =title)
plt.figsize(16,4)
fig.close()
except Exception:
title = 'not found'
<matplotlib.figure.Figure at 0x1e3659d0>
